
//Draw Long Rates of Return from Schmelzing, Clark etc in Figures A1





import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(IV. Country level, 1310-2018) clear
keep A AE-AL
replace A="1309" if _n==3
drop if _n<=2
drop if _n>=711
destring A-AL, replace
rename A year
rename (AE-AL) temp#, addnumber
reshape long temp , i(year) j(cty) string
gen countrycode="ITA"
replace countrycode="GBR" if cty=="2"
replace countrycode="NLD" if cty=="3"
replace countrycode="DEU" if cty=="4"
replace countrycode="FRA" if cty=="5"
replace countrycode="USA" if cty=="6"
replace countrycode="ESP" if cty=="7"
replace countrycode="JPN" if cty=="8"
drop cty
rename temp nint
sort countrycode year

tempfile nint
save `nint'

//import country-level inflation rates from Schmelzing
import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(IV. Country level, 1310-2018) clear


keep A U-AB
drop if _n<=3
drop if _n>=710
destring A-AB, replace
rename A year
rename (U-AB) temp#, addnumber
reshape long temp , i(year) j(cty) string
gen countrycode="ITA"
replace countrycode="GBR" if cty=="2"
replace countrycode="NLD" if cty=="3"
replace countrycode="DEU" if cty=="4"
replace countrycode="FRA" if cty=="5"
replace countrycode="USA" if cty=="6"
replace countrycode="ESP" if cty=="7"
replace countrycode="JPN" if cty=="8"
drop cty
rename temp inf
sort countrycode year
merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year


save `nint', replace




//import country-level nominal land returns from Clark'88



import excel "$base/InputData/Clark1988-RentChargesCrossCountryClean.xlsx", clear first
drop italyrentwheat italyrentmoney

local new = _N + 1
set obs `new'
replace year=1150 if _n==14
sort year

local new = _N + 1
set obs `new'
replace year=1800 if _n==15
sort year

rename (england- italyland) temp#, addnumber

reshape long temp , i(year) j(cty) 

gen countrycode="GBR"
replace countrycode="FRA" if cty==2
replace countrycode="NLD" if cty==3
replace countrycode="DEU" if cty==4
replace countrycode="ITA" if cty==5

//We already show more up to date GBR data in the main body of the paper
//drop if countrycode=="GBR"

drop cty
rename temp nintLand
order countrycode, before(year)
sort countrycode year

//gen landreturns=1 if nintLand!=.
//replace landreturns=1 

/*bys countrycode: gen temp=_n
encode countrycode, gen(cc)
xtset cc temp
gen landreturns=1 if nintLand!=.
gen tempx=1 if l.landreturns==1
replace landreturns=tempx if landreturns==.
drop if landreturns==.
*/

encode countrycode, gen(cty)
xtset cty year
tsfill
decode cty, gen(test)
replace countrycode=test
drop test
bys cty: replace nintLand=l.nintLand if nintLand==.
drop cty


drop if year<= 1249 & countrycode=="DEU"
drop if year>= 1551 & countrycode=="DEU"


drop if year<= 1199 & countrycode=="FRA"
drop if year>= 1801 & countrycode=="FRA"

drop if year<= 1249 & countrycode=="NLD"
drop if year>= 1801 & countrycode=="NLD"

drop if year<= 1199 & countrycode=="ITA"
drop if year>= 1801 & countrycode=="ITA"

/*
drop if year>=1301 & year<= 1450  & countrycode=="FRA"
drop if year>=1651 & year<= 1700 & countrycode=="FRA"

drop if year>=1301 & year<= 1450 & countrycode=="NLD"
drop if year>=1501 & year<= 1550 & countrycode=="NLD"

drop if year>=1551 & year<= 1800 & countrycode=="DEU"

drop if year>=1501 & year<= 1750 & countrycode=="ITA"
*/
keep countrycode year nintLand


sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year


save `nint', replace



// Import Global real rate of return on land from Schmelzing

import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(VI. R-G variations, 1317-2018) clear


keep A K
drop if _n<=11
drop if _n>=670




destring A K, replace
rename A year
gen countrycode="WLD"
rename K rintGlobalLand
sort countrycode year

merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year

save `nint', replace



// Import US real rate of return on land from Schmelzing via Featherstone and Baker, 1987

import excel "$base/InputData/FeatherstoneBaker1987_USFarmLandReturns.xlsx", clear


keep A-C
drop if _n<=3


destring A-C, replace
rename A year
gen countrycode="USA"
gen rintUSALand=C/B
drop C B
sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year

save `nint', replace


//import personal/non marketable sovereign loans from Schmelzing

import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(VIII. Pers. sov. loans, 1312-) clear

keep A Z AA
replace A="1311" if _n==4
drop if _n<=3
destring A-AA, replace
rename A year
rename Z nintPersonal
rename AA infPersonal
gen countrycode="WLD"

sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year

save `nint', replace



//import private R from Schmelzing

import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(II. Headline series) clear

keep A W X

//replace A="1311" if _n==4
drop if _n<=7
drop if _n>=710


destring A-X, replace
rename A year
rename W nintPrivate
rename X infPrivate
gen countrycode="WLD"

replace nintPrivate=. if infPrivate==.

sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year

save `nint', replace










//import country-level GDP shares from Schmelzing

import excel "$base/InputData/eight-centuries-of-global-real-interest-rates-r-g-and-the-suprasecular-decline-1311-2018-data.xlsx", sheet(III. GDP shares, 1310-2018) clear


keep A C-J
drop if _n<=5
drop if _n>=710
destring A-J, replace
rename A year
rename (C-J) temp#, addnumber
reshape long temp , i(year) j(cty) string
gen countrycode="ITA"
replace countrycode="GBR" if cty=="2"
replace countrycode="NLD" if cty=="3"
replace countrycode="DEU" if cty=="4"
replace countrycode="FRA" if cty=="5"
replace countrycode="USA" if cty=="6"
replace countrycode="ESP" if cty=="7"
replace countrycode="JPN" if cty=="8"
drop cty
rename temp gdpSh
sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year
save `nint', replace


//Construct inflation and nominal inflation needd for Schemelzing's Safe series 

keep countrycode year inf nint
reshape wide inf nint, i(year) j(countrycode) string


foreach j in "inf" "nint"{
gen `j'Safe=.
/*replace `j'Safe =`j'ITA if year<=1509
replace `j'Safe =`j'ESP if year>=1510 & year<=1598
replace `j'Safe =`j'NLD if year>=1599 & year<=1702
replace `j'Safe =`j'GBR if year>=1703 & year<=1907
replace `j'Safe =`j'DEU if year>=1908 & year<=1913
replace `j'Safe =`j'GBR if year>=1914 & year<=1918
replace `j'Safe =`j'USA if year>=1919 & year<=1961
replace `j'Safe =`j'DEU if year>=1962 & year<=1980
replace `j'Safe =`j'USA if year>=1981 & year<=2018*/

replace `j'Safe =`j'ITA if year<=1508
replace `j'Safe =`j'ESP if year>=1509 & year<=1598
replace `j'Safe =`j'NLD if year>=1599 & year<=1702
replace `j'Safe =`j'GBR if year>=1703 & year<=1919
replace `j'Safe =`j'USA if year>=1920 & year<=2018
}
gen countrycode="WLD"
keep countrycode year infSafe nintSafe
sort countrycode year


merge countrycode year using `nint'
drop _merge
order countrycode, before(year)

sort countrycode year
save `nint', replace




// Construct real rates of return

encode countrycode, gen(cty)
xtset cty year
tsfill
bys cty: ipolate nint year, gen(nintipol)
bys cty: ipolate inf year, gen(ninfipol)

//This inflation rate comes from Clark 1988
replace ninfipol=0.5 if year>=1201 & year<=1309 & countrycode=="GBR" 



gen cpi= .
replace cpi=1  if f.ninfipol!=.
bys cty: replace cpi=(1+ninfipol/100)*l.cpi if ninfipol!=.

gen nreturns= .
replace nreturns=1  if f.nintipol!=.
bys cty: replace nreturns=(1+nintipol/100)*l.nreturns if nintipol!=.



gen cpiPersonal= .
replace cpiPersonal=1  if f.infPersonal!=.
bys cty: replace cpiPersonal=(1+infPersonal/100)*l.cpiPersonal if infPersonal!=.

gen nreturnsPersonal= .
replace nreturnsPersonal=1  if f.nintPersonal!=.
bys cty: replace nreturnsPersonal=(1+nintPersonal/100)*l.nreturnsPersonal if nintPersonal!=.



gen cpiPrivate= .
replace cpiPrivate=1  if f.infPrivate!=.
bys cty: replace cpiPrivate=(1+infPrivate/100)*l.cpiPrivate if infPrivate!=.

gen nreturnsPrivate= .
replace nreturnsPrivate=1  if f.nintPrivate!=.
bys cty: replace nreturnsPrivate=(1+nintPrivate/100)*l.nreturnsPrivate if nintPrivate!=.



gen cpiSafe= .
replace cpiSafe=1  if f.infSafe!=.
bys cty: replace cpiSafe=(1+infSafe/100)*l.cpiSafe if infSafe!=.

gen nreturnsSafe= .
replace nreturnsSafe=1  if f.nintSafe!=.
bys cty: replace nreturnsSafe=(1+nintSafe/100)*l.nreturnsSafe if nintSafe!=.






gen nlandreturns= .
replace nlandreturns=1  if f.nintLand!=.
bys cty: replace nlandreturns=(1+nintLand/100)*l.nlandreturns if nintLand!=.


gen rGlobalLandReturns= .
replace rGlobalLandReturns=1  if f.rintGlobalLand!=.
bys cty: replace rGlobalLandReturns=(1+rintGlobalLand/100)*l.rGlobalLandReturns if rintGlobalLand!=.



gen rUSALandReturns= .
replace rUSALandReturns=1  if f.rintUSALand!=.
bys cty: replace rUSALandReturns=(1+rintUSALand)*l.rUSALandReturns if rintUSALand!=.





gen temp = cpi if year==2010
bys cty: egen temp2=min(temp)
gen cpi2010=cpi/temp2
drop temp temp2


gen temp = cpiPersonal if year==2010
bys cty: egen temp2=min(temp)
gen cpiPersonal2010=cpiPersonal/temp2
drop temp temp2


gen temp = cpiPrivate if year==2010
bys cty: egen temp2=min(temp)
gen cpiPrivate2010=cpiPrivate/temp2
drop temp temp2


gen temp = cpiSafe if year==2010
bys cty: egen temp2=min(temp)
gen cpiSafe2010=cpiSafe/temp2
drop temp temp2


gen rreturnsPrivate=nreturnsPrivate/cpiPrivate2010
gen rGenPrivate=(f24.rreturnsPrivate/rreturnsPrivate)^(1/24)-1

gen rreturnsPersonal=nreturnsPersonal/cpiPersonal2010
gen rGenPersonal=(f24.rreturnsPersonal/rreturnsPersonal)^(1/24)-1

gen rreturnsSafe=nreturnsSafe/cpiSafe2010
gen rGenSafe=(f.rreturnsSafe/rreturnsSafe)^(1/1)-1


gen rreturns=nreturns/cpi2010
gen rGen=(f24.rreturns/rreturns)^(1/24)-1
gen rAnnual=(f.rreturns/rreturns)-1



gen rlandreturns=nlandreturns/cpi2010
gen rLandGen=(f24.rlandreturns/rlandreturns)^(1/24)-1
//gen rLandGen=(f.rlandreturns/rlandreturns)-1
gen temp =(f24.rGlobalLandReturns/rGlobalLandReturns)^(1/24)-1
replace rLandGen=temp if countrycode=="WLD"
drop temp

gen temp =(f24.rUSALandReturns/rUSALandReturns)^(1/24)-1
replace rLandGen=temp if countrycode=="USA"
drop temp



sort countrycode year
tempfile nint
save `nint'


gen temp= rGen* gdpSh
collapse (sum) temp, by(year)
replace temp=. if temp==0
rename temp rGen
gen countrycode="WLD"
sort countrycode year


merge countrycode year using `nint'
drop _merge  
order countrycode, before(year)

sort countrycode year

drop cty
encode countrycode, gen(cty)
xtset cty year


replace rGenPrivate=rGenPrivate*100
label var rGenPrivate "rGenPrivate"

replace rGenPersonal=rGenPersonal*100
label var rGenPersonal "rGenPersonal"

replace rGenSafe=rGenSafe*100
label var rGenSafe "rGenSafe"

replace rGen=rGen*100
label var rGen "rGen"
//xtline rGen


replace rGen=. if countrycode=="ESP" & year<=1799 & year>=1731

replace rLandGen=rLandGen*100
label var rLandGen "rLandGen"
//xtline rLandGen if rLandGen!=.



//replace rLandGen=. if countrycode=="ITA" & year>=1501 & year<=1750 
//replace rLandGen=. if countrycode=="NLD" & year>=1301 & year<=1450 


//gen temp=100*(((rreturns)/l.rreturns)-1)
//gen rint=nint-inf
sort countrycode year

save "$base/Outputs/Data/interestRatesAll.dta", replace

gen yearave=1225

 foreach num of numlist 1325(50)2050 {
                replace yearave=`num' if year<=`num'+26 & year>`num'-26
        }
		
		

bys countrycode yearave : egen temp=mean(rGen)

collapse (median) rLandGen rGen rGenPersonal rGenPrivate rGenSafe, by(yearave countrycode)
drop if yearave>=2000
sort countrycode yearave
encode countrycode, gen(cty)
xtset cty yearave


label var rLandGen "Annual real rate, \%"
label var yearave "Year"
//xtline rLandGen if rLandGen!=. & countrycode!="GBR",  recast(connected)  byopt(note("") title("Real return on land")) scheme(lean1) 


egen group = group(cty)
gen fittedrGen = .
su group, meanonly

forval g = 1/`r(max)' {
    regress rGen year if group == `g'
	scalar ax`g' = _b[year]
	scalar bx`g' = _b[_cons]
    predict p
    replace fittedrGen = p if group == `g'  
    drop p
}


gen fittedrLandGen = .
su group, meanonly

forval g = 1/`r(max)'{

if `g'==1 |`g'==3 |`g'==4 | `g'==5| `g'==7| `g'==8 | `g'==9 { 
    regress rLandGen year if group == `g' 
    predict p
    replace fittedrLandGen = p if group == `g'  
    drop p
	}
}
replace fittedrLandGen=. if rLandGen==.


set graphics off
//Real Returns on land or rent-charges
xtline rLandGen fittedrLandGen  if fittedrLandGen!=. & fittedrLandGen!=. & countrycode!="USA" ,    byopt(note("") title("") legend(off) ) ytitle("Annual Real Rate, %") scheme(lean1) 
graph export  "$base/Outputs/Graphs/rLandGenCountries.png", replace 

//ttext(1 1720 "helo") 
//Long-term debt yields
xtline rGen fittedrGen if rGen!=.  & fittedrGen!=. ,   byopt(note("") title("") legend(off) )  ytitle("Annual Real Rate, %") xlabel(1300(200)1900) scheme(lean1) 
graph export  "$base/Outputs/Graphs/rGenCountries.png", replace  

gr tw (line rGenPersonal year) (lfit rGenPersonal year), scheme(lean1) title("Personal/non-marketable sovereign loans") legend(off)  ytitle("Annual Real Rate, %") ylabel(-5(5)15)
graph export  "$base/Outputs/Graphs/rGenPersonal.png", replace  

gr tw (line rGenPrivate year) (lfit rGenPrivate year), scheme(lean1) title("Private, secured mortgage market loans") legend(off)  ytitle("Annual Real Rate, %") ylabel(-5(5)15)
graph export  "$base/Outputs/Graphs/rGenPrivate.png", replace  


set graphics on
